﻿DECLARE @StatDate datetime
SET @StatDate = DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))
/* основная обновлялка, учет входящих от АП, кол-во, объем */
UPDATE statistica 
SET calls_prin = msg_input,
    prinb = msg_kb
FROM statistica st JOIN
     (SELECT
        se.address_x_121,
        count(md.hostname_id) AS msg_input,
        sum(md.msg_size) as msg_kb
      FROM mon400_data md, stat_elements se 
      WHERE
        (md.datetime_in BETWEEN (@StatDate) AND (@StatDate + 1)) and
        (md.hostname_id = se.node) AND
        (md.msg_kind = 0) AND /* только входящие письма от АП */
        (md.msg_cirkul = 0)   /* уберем лишние циркуляры */
GROUP BY se.address_x_121) 
AS
 sum_select ON (st.[address] =  address_x_121) and
 (st.dbegin_stat = @StatDate)
/* вставка кол-ва ошибочных */
UPDATE statistica
SET calls_pered = msg_err
FROM statistica st JOIN
     (SELECT
        se.address_x_121,
        count(md.hostname_id) AS msg_err
      FROM mon400_data md, stat_elements se 
      WHERE
        (md.datetime_in BETWEEN (@StatDate) AND (@StatDate + 1)) and
        (md.hostname_id = se.node) AND
        (md.msg_kind = 2) AND      /* report re - входящие отчеты */
        (md.msg_errorcode_id <> 6) /* т.е. не ОК - значит ошибка  */
GROUP BY se.address_x_121) 
AS
 sum_select ON (st.[address] =  address_x_121) and
 (st.dbegin_stat  = @StatDate)
/* вставка кол-ва срочных */
UPDATE statistica
SET calls_pered = msg_err
FROM statistica st JOIN
     (SELECT
        se.address_x_121,
        count(md.hostname_id) AS msg_err
      FROM mon400_data md, stat_elements se 
      WHERE
        (md.datetime_in BETWEEN (@StatDate) AND (@StatDate + 1)) and
        (md.hostname_id = se.node) AND
        (md.msg_kind = 0) AND   /* только входящие письма от АП */
        (md.msg_cirkul = 0) AND /* уберем лишние циркуляры */
        (md.msg_category = 2)   /* срочные */
GROUP BY se.address_x_121) 
AS
 sum_select ON (st.[address] =  address_x_121) and
 (st.dbegin_stat  = @StatDate)